﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using Eagleshoes.PackingBarcode.CartonService;

namespace Eagleshoes.PackingBarcode.PackagingScan
{
    public class MyPackagingCarton
    {
        private Dictionary<string,MyPackagingBox> boxes;
        private bool isScan;

        public MyPackagingCarton()
        {
            boxes = new Dictionary<string, MyPackagingBox>();
        }

        public MyPackagingCarton(PackagingCarton packaging)
            : this()
        {
            CartonBarcode = packaging.CartonBarcode;
            SalesOrderCode = packaging.SalesOrderCode;
            PackagingOrderCode = packaging.PackagingOrderCode;
            boxes = (from box in packaging.Boxes
                     orderby box.GoodsCode ascending 
                     select new MyPackagingBox(box)).ToDictionary(e => e.BoxBarcode);
        }

        /// <summary>
        /// 外箱条码
        /// </summary>
        public string CartonBarcode
        {
            get;
            set;
        }

        /// <summary>
        /// 销售订单号
        /// </summary>
        public string SalesOrderCode
        {
            get;
            set;
        }

        /// <summary>
        /// 包装单号
        /// </summary>
        public string PackagingOrderCode
        {
            get;
            set;
        }

        /// <summary>
        /// 是否已扫描并满箱
        /// </summary>
        public bool IsScan
        {
            get
            {
                return isScan && IsFull;
            }
            set
            {
                isScan = value;
            }
        }

        public DateTime? ScanTime
        {
            get;
            set;
        }

        /// <summary>
        /// 是否已保存至服务器
        /// </summary>
        public bool IsSavedToServer
        {
            get;
            set;
        }

        public DateTime? SavedTime
        {
            get;
            set;
        }

        /// <summary>
        /// 是否满箱
        /// </summary>
        public bool IsFull
        {
            get
            {
                return boxes.Values.All(box => box.Quantities == box.ScanedQuantities);
            }
        }

        public Dictionary<string,MyPackagingBox> Boxes
        {
            get
            {
                return boxes;
            }
        }
 

        #region Data Action

        /// <summary>
        /// 从服务器获取包装信息
        /// </summary>
        /// <param name="cartonBarcode"></param>
        /// <returns></returns>
        public static MyPackagingCarton GetPackagingCartonOnline(string cartonBarcode)
        {
            CartonService.CartonService cs = Program.CartonClient;
            PackagingCarton pack = cs.GetPackagingCartonOnline(cartonBarcode);
            return pack == null ? null : new MyPackagingCarton(pack);
        }

        public static List<MyPackagingCarton> GetPackagingCartonListOnline()
        {
            CartonService.CartonService cs = Program.CartonClient;
            PackagingCarton[] packagingCartonList = cs.GetCartonListFromBoxBarDownTable();

            return (from pc in packagingCartonList
                    select new MyPackagingCarton(pc)).ToList();
        }

        /// <summary>
        /// 保存包装扫描数据至服务器
        /// </summary>
        /// <param name="packagings"></param>
        /// <returns></returns>
        public static bool SaveScanedPackagingCartonOnline(List<MyPackagingCarton> packagings)
        {
            var pcss = new List<PackagingScaned>();
            foreach (MyPackagingCarton pc1 in (from pc in packagings
                                               where pc.IsFull && pc.ScanTime.HasValue
                                               select pc).ToList())
            {
                pcss.AddRange((from box in pc1.boxes.Values
                               select
                                   new PackagingScaned
                                       {
                                           BoxBarcode = box.BoxBarcode,
                                           CartonBarcode = box.CartonBarcode,
                                           Quantities = box.Quantities,
                                           ScanedDate = pc1.ScanTime.Value
                                       }).ToList());
            }
            return Program.CartonClient.SavePackaginScanedData(pcss.ToArray());
        }

        //-----------离线-----------


        /// <summary>
        /// 从离线数据库在加载数据
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="cartonBarcode"></param>
        /// <returns></returns>
        public static MyPackagingCarton GetPackagingCarton(SQLiteConnection connection, string cartonBarcode)
        {
            MyPackagingCarton pc = null;
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }

            List<MyPackagingBox> mypbList = MyPackagingBox.GetPackagingBoxList(connection, cartonBarcode);

            SQLiteCommand cmd = connection.CreateCommand();
            cmd.CommandText = @"SELECT CartonBarcode,SalesOrderCode,PackagingOrderCode,IsScan,ScanTime,IsUpload,UploadTime FROM Carton WHERE CartonBarcode=?";
            cmd.Parameters.Add(new SQLiteParameter("@barcode", DbType.String));
            cmd.Parameters["@barcode"].Value = cartonBarcode;
            SQLiteDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                pc = new MyPackagingCarton
                    {
                        CartonBarcode = dr.GetString(0),
                        SalesOrderCode = dr.GetString(1),
                        PackagingOrderCode = dr.GetString(2),
                        IsScan = dr.GetBoolean(3),
                        ScanTime = dr.IsDBNull(4) ? null : new DateTime?(dr.GetDateTime(4)),
                        IsSavedToServer = dr.GetBoolean(5),
                        SavedTime = dr.IsDBNull(6) ? null : new DateTime?(dr.GetDateTime(6)),
                        boxes=mypbList.ToDictionary(box=>box.BoxBarcode)
                    };
            }
            dr.Close();
            return pc;
        }

        public static List<MyPackagingCarton> GetScanedAndNonUploadPackagingCartonList(SQLiteConnection connection)
        {
            List<MyPackagingBox> mypbList = MyPackagingBox.GetScanedAndNonUploadPackagingBoxList(connection);

            var mypcList = new List<MyPackagingCarton>();
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }


            SQLiteCommand cmd = connection.CreateCommand();
            cmd.CommandText = @"
SELECT   CartonBarcode, SalesOrderCode, PackagingOrderCode, IsScan, ScanTime, IsUpload, UploadTime
FROM      Carton c
WHERE   (IsScan = 1) AND (IsUpload = 0) AND (CartonBarcode NOT IN
                    (SELECT   CartonBarcode
                     FROM      Box b
                     WHERE   (Quantities <> ScanedQuantities)))";
            SQLiteDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                mypcList.Add(new MyPackagingCarton
                    {
                        CartonBarcode = dr.GetString(0),
                        SalesOrderCode = dr.GetString(1),
                        PackagingOrderCode = dr.GetString(2),
                        IsScan = dr.GetBoolean(3),
                        ScanTime = dr.IsDBNull(4) ? null : new DateTime?(dr.GetDateTime(4)),
                        IsSavedToServer = dr.GetBoolean(5),
                        SavedTime = dr.IsDBNull(6) ? null : new DateTime?(dr.GetDateTime(6)),
                        boxes = (from box in mypbList
                                 where box.CartonBarcode == dr.GetString(0)
                                 select box).ToDictionary(e => e.BoxBarcode)
                    });
            }
            dr.Close();
            return mypcList;
        }

 
        public static bool MarkToScaned(SQLiteConnection connection, string cartonBarcode, bool isScan, DateTime scanTime)
        {
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }

            SQLiteCommand cmd = connection.CreateCommand();
            cmd.Parameters.AddWithValue("@CartonBarcode", cartonBarcode);
            cmd.Parameters.AddWithValue("@ScanTime", scanTime);

            if (isScan)
            {
                cmd.CommandText = @"
UPDATE Box SET ScanedQuantities = Quantities WHERE CartonBarcode=@CartonBarcode; 
UPDATE Carton set 
    IsScan=1,
    ScanTime=@ScanTime 
WHERE CartonBarcode=@CartonBarcode and IsScan<>1";
            }
            else
            {
                cmd.CommandText = @"
UPDATE Box SET ScanedQuantities = 0 WHERE CartonBarcode=@CartonBarcode; 
UPDATE Carton set 
    IsScan=0,
    ScanTime=null,
    IsUpload=0,
    UploadTime=null 
WHERE CartonBarcode=@CartonBarcode";
            }

            int re = cmd.ExecuteNonQuery();
            connection.Close();
            return re > 0;
        }

        public static bool MarkToUploaded(SQLiteConnection connection, string cartonBarcode, bool isUpload)
        {
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }

            SQLiteCommand cmd = connection.CreateCommand();
            cmd.Parameters.AddWithValue("@CartonBarcode", cartonBarcode);
            cmd.Parameters.AddWithValue("@UploadTime", DateTime.Now);

            if (isUpload)
            {
                cmd.CommandText = @" 
UPDATE Carton set 
    IsUpload=1,
    ScanTime=@UploadTime 
WHERE CartonBarcode=@CartonBarcode and IsScan=1";
            }
            else
            {
                cmd.CommandText = @"
UPDATE Carton set 
    IsUpload=0,
    UploadTime=null 
WHERE CartonBarcode=@CartonBarcode";
            }

            int re = cmd.ExecuteNonQuery();
            connection.Close();
            return re > 0;
        }


        public static bool SaveToLocal(SQLiteConnection connection, PackagingCarton packaging)
        {
            MyPackagingCarton mypc = GetPackagingCarton(connection, packaging.CartonBarcode);
            if (mypc == null)
            {
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }
                SQLiteTransaction tr = connection.BeginTransaction();
                SQLiteCommand cmd = connection.CreateCommand();
                cmd.Parameters.Add(new SQLiteParameter("@CartonBarcode", DbType.String));
                cmd.Parameters.Add(new SQLiteParameter("@SalesOrderCode", DbType.String));
                cmd.Parameters.Add(new SQLiteParameter("@PackagingOrderCode", DbType.String));
                cmd.Parameters.Add(new SQLiteParameter("@BoxBarcode", DbType.String));
                cmd.Parameters.Add(new SQLiteParameter("@GoodsCode", DbType.String));
                cmd.Parameters.Add(new SQLiteParameter("@Spec", DbType.String));
                cmd.Parameters.Add(new SQLiteParameter("@Quantities", DbType.String));

                cmd.Transaction = tr;
                try
                {
                    cmd.CommandText = "Delete FROM Box WHERE CartonBarcode=@CartonBarcode";
                    cmd.Parameters["@CartonBarcode"].Value = packaging.CartonBarcode;
                    cmd.ExecuteNonQuery();

                    cmd.CommandText =
                        "INSERT INTO Carton (CartonBarcode,SalesOrderCode,PackagingOrderCode) VALUES (@CartonBarcode,@SalesOrderCode,@PackagingOrderCode)";
                    cmd.Parameters["@SalesOrderCode"].Value = packaging.SalesOrderCode;
                    cmd.Parameters["@PackagingOrderCode"].Value = packaging.PackagingOrderCode;
                    cmd.ExecuteNonQuery();

                    cmd.CommandText =
                        "INSERT INTO Box (GoodsCode,BoxBarcode,CartonBarcode,Spec,Quantities,ScanedQuantities) VALUES (@GoodsCode,@BoxBarcode,@CartonBarcode,@Spec,@Quantities,0)";
                    foreach (PackagingBox box in packaging.Boxes)
                    {
                        cmd.Parameters["@GoodsCode"].Value = box.GoodsCode;
                        cmd.Parameters["@BoxBarcode"].Value = box.BoxBarcode;
                        cmd.Parameters["@Spec"].Value = box.Spec;
                        cmd.Parameters["@Quantities"].Value = box.Quantities;
                        cmd.ExecuteNonQuery();
                    }
                    tr.Commit();
                    return true;
                }
                catch
                {
                    tr.Rollback();
                    return false;
                }
            }
            return false;
        }

        #endregion

        #region Class:MyPackagingBox

        /// <summary>
        /// 包装内盒
        /// </summary>
        public class MyPackagingBox
        {
            public MyPackagingBox()
            {
            }

            public MyPackagingBox(PackagingBox box)
            {
                BoxBarcode = box.BoxBarcode;
                CartonBarcode = box.CartonBarcode;
                GoodsCode = box.GoodsCode;
                Quantities = box.Quantities;
                Spec = box.Spec;
            }

            /// <summary>
            /// 内盒条码
            /// </summary>
            public string BoxBarcode
            {
                get;
                set;
            }

            public string CartonBarcode
            {
                get;
                set;
            }

            public string GoodsCode
            {
                get;
                set;
            }

            public string Spec
            {
                get;
                set;
            }

            public int Quantities
            {
                get;
                set;
            }

            public int ScanedQuantities
            {
                get;
                set;
            }

            #region DataAction

            public static List<MyPackagingBox> GetPackagingBoxList(SQLiteConnection connection, string cartonBarcode)
            {
                var pbList = new List<MyPackagingBox>();

                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }
                SQLiteCommand cmd = connection.CreateCommand();
                cmd.CommandText = @"SELECT CartonBarcode,BoxBarcode,GoodsCode,Spec,Quantities,ScanedQuantities FROM Box WHERE CartonBarcode=@barcode";
                cmd.Parameters.Add(new SQLiteParameter("@barcode", DbType.String));
                cmd.Parameters["@barcode"].Value = cartonBarcode;
                SQLiteDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    pbList.Add(new MyPackagingBox
                        {
                            CartonBarcode = dr.GetString(0),
                            BoxBarcode = dr.GetString(1),
                            GoodsCode = dr.GetString(2),
                            Spec = dr.IsDBNull(3) ? string.Empty : dr.GetString(3),
                            Quantities = dr.GetInt32(4),
                            ScanedQuantities = dr.GetInt32(5)
                        });
                }
                dr.Close();

                return pbList;
            }

            public static List<MyPackagingBox> GetScanedAndNonUploadPackagingBoxList(SQLiteConnection connection)
            {
                var pbList = new List<MyPackagingBox>();

                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }
                SQLiteCommand cmd = connection.CreateCommand();
                cmd.CommandText = @"SELECT b.CartonBarcode,b.BoxBarcode,b.GoodsCode,b.Spec,b.Quantities,b.ScanedQuantities 
FROM Box as b 
INNER JOIN Carton as c 
    ON b.CartonBarcode = c.CartonBarcode 
WHERE c.IsScan = 1 
    AND c.IsUpload=0 
    AND b.Quantities = b.ScanedQuantities";

                SQLiteDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    pbList.Add(new MyPackagingBox
                        {
                            CartonBarcode = dr.GetString(0),
                            BoxBarcode = dr.GetString(1),
                            GoodsCode = dr.GetString(2),
                            Spec = dr.IsDBNull(3) ? string.Empty : dr.GetString(3),
                            Quantities = dr.GetInt32(4),
                            ScanedQuantities = dr.GetInt32(5)
                        });
                }
                dr.Close();

                return pbList;
            }

            #endregion
        }

        #endregion
    }
}